-- PostgreSQL
--   database: jdbc:postgresql:db.cjprods.org:5432/ars
--   username: ars
--   password: ars
--------------------------------------------------------------------------------
-- !!!!!!!! This user and database will be REMOVED after 2016-02-22 !!!!!!!!
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
---- DESIGN
-- User `ars` is UNDERPRIVILEGED, and can ONLY select from functions.

--------------------------------------------------------------------------------
---- Error codes:
-- 'NOTFD': SOMETHING not found

--------------------------------------------------------------------------------
-- Transaction begin
BEGIN;

-- <editor-fold desc="reset database">
--------------------------------------------------------------------------------
-- reset database
--/*
DROP SCHEMA public CASCADE;
DROP ROLE ars;
--*/

--------------------------------------------------------------------------------
-- Schema: public
CREATE SCHEMA public
  AUTHORIZATION postgres;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public
  IS 'standard public schema';
-- </editor-fold>

-- <editor-fold desc="types">
-- <editor-fold desc="role">
--------------------------------------------------------------------------------
-- Type: role 角色
CREATE TYPE role AS ENUM (
  'admin',          -- 管理员
  'user'            -- 注册用户
);
-- </editor-fold>

-- <editor-fold desc="order_state">
--------------------------------------------------------------------------------
-- Type: order_state 订单状态
CREATE TYPE order_state AS ENUM (
  'fresh',          -- 刚刚下单
  'delivered',      -- 车辆已租出
  'returned',       -- 车辆已归还
  'canceled'        -- 取消
);
/*  valid state transitions:
            fresh -> delivered
            fresh -> canceled
        delivered -> returned

    or, the other way around:
        fresh     <- ON-CREATION
        delivered <- fresh
        returned  <- delivered
        canceled  <- fresh
*/
-- </editor-fold>

-- </editor-fold>

-- <editor-fold desc="tables">
-- <editor-fold desc="users">
--------------------------------------------------------------------------------
-- Table: users 用户
CREATE TABLE users
(
  id serial NOT NULL,
  seq serial NOT NULL,          -- 登录令牌
  username text NOT NULL,       -- 用户名（长度在48码点以内）
  credential text NOT NULL,     -- 登录凭证，bcrypt 加盐哈希后的密码（密码长度在48码点以内）
  role role NOT NULL,           -- 角色（权限）
  name text NOT NULL,           -- 姓名（长度在48码点以内）
  phone text NOT NULL,          -- 手机号码或电话号码（长度在24码点以内）
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_seq_unique UNIQUE (seq),
  CONSTRAINT users_username_unique UNIQUE (username),
  CONSTRAINT users_username_check CHECK (length(username) BETWEEN 1 AND 48),
  CONSTRAINT users_name_check CHECK (length(name) BETWEEN 1 AND 48),
  CONSTRAINT users_phone_check CHECK (length(phone) BETWEEN 0 AND 24)   -- 管理员的手机号长度为0
);
-- </editor-fold>

-- <editor-fold desc="newss">
--------------------------------------------------------------------------------
-- Table: newss 快讯
CREATE TABLE newss
(
  id serial NOT NULL,
  body text NOT NULL,               -- 快讯正文
  date timestamp NOT NULL,          -- 发布日期
  CONSTRAINT newss_pkey PRIMARY KEY (id)
);
-- </editor-fold>

-- <editor-fold desc="autos">
--------------------------------------------------------------------------------
-- Table: autos 车辆
CREATE TABLE autos
(
  id serial NOT NULL,
  name text NOT NULL,               -- 车辆名称
  description text NOT NULL,        -- 车辆描述
  price integer NOT NULL,           -- 租赁价格
  removed boolean NOT NULL,         -- 已删除标记
  CONSTRAINT autos_pkey PRIMARY KEY (id),
  CONSTRAINT autos_price_check CHECK (price > 0)
);
-- </editor-fold>

-- <editor-fold desc="orders">
--------------------------------------------------------------------------------
-- Table: orders 订单
CREATE TABLE orders
(
  id serial NOT NULL,
  orderer integer NOT NULL,         -- 下单者
  auto integer NOT NULL,            -- 车辆
  state order_state NOT NULL,       -- 订单状态
  CONSTRAINT orders_pkey PRIMARY KEY (id),
  CONSTRAINT orders_user_fkey FOREIGN KEY (orderer) REFERENCES users ON DELETE CASCADE,
  CONSTRAINT orders_auto_fkey FOREIGN KEY (auto   ) REFERENCES autos ON DELETE CASCADE
);
-- </editor-fold>

-- <editor-fold desc="feedbacks">
--------------------------------------------------------------------------------
-- Table: feedbacks 反馈
CREATE TABLE feedbacks
(
  id serial NOT NULL,
  who integer NOT NULL,             -- 反馈者
  body text NOT NULL,               -- 反馈正文（128个码点（codepoint））
  reply text NULL,                  -- 管理员回复
  CONSTRAINT feedbacks_pkey PRIMARY KEY (id),
  CONSTRAINT feedbacks_who_fkey FOREIGN KEY (who) REFERENCES users ON DELETE CASCADE,
  CONSTRAINT feedbacks_body_check CHECK (length(body) BETWEEN 1 AND 128)
);
-- </editor-fold>

-- <editor-fold desc="online_autos">
--------------------------------------------------------------------------------
-- View: online_autos 未下架车辆
CREATE VIEW online_autos AS
 SELECT DISTINCT ON (autos.id) autos.id,
    autos.name,
    autos.description,
    autos.price,
    orders.state IS NULL OR orders.state = 'returned' OR orders.state = 'canceled' AS available
   FROM autos LEFT JOIN orders ON autos.id = orders.auto
  WHERE autos.removed = false
  ORDER BY autos.id DESC, orders.id DESC;
-- </editor-fold>

-- <editor-fold desc="verbose_orders">
--------------------------------------------------------------------------------
-- View: verbose_orders 订单详情
CREATE VIEW verbose_orders AS
 SELECT orders.id,
    orders.orderer AS orderer_id,
    format('%s（%s）'::text, users.name, users.username) AS orderer,
    users.phone,
    autos.name AS auto_name,
    autos.description AS auto_description,
    autos.price,
    orders.state
   FROM orders
     JOIN users ON orders.orderer = users.id
     JOIN autos ON orders.auto = autos.id;
-- </editor-fold>

-- </editor-fold>

-- <editor-fold desc="functions">
-- <editor-fold desc="users">
--------------------------------------------------------------------------------
-- Function: () -> ()
CREATE FUNCTION bootstrap()
  RETURNS void AS
$BODY$
BEGIN
    -- user admin, password admin
    INSERT INTO users(username, credential, role, name, phone) VALUES
            ('admin', '$2a$10$qpEAwMIZn.upbjnhcZ4cFO8EUcA8MiwOlLEwmBxcW9AWQ4Km9nM.2', 'admin', '管理员', '');
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text, text, text, text) -> ()
CREATE FUNCTION add_user(username_ text, credential_ text, name_ text, phone_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO users(username, credential, role, name, phone)
        VALUES (username_, credential_, 'user', name_, phone_);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION revoke_user(user_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET seq = default WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text) -> (integer, text, role, text, text)
CREATE FUNCTION user_info(username_ text)
  RETURNS TABLE(seq_ integer, credential_ text, role_ role, name_ text, phone_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT seq, credential, role, name, phone
          FROM users WHERE username = username_;
    IF not found THEN raise 'no such username' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (text, text, text)
CREATE FUNCTION validate_user(user_ text)
  RETURNS TABLE(username_ text, name_ text, phone_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT username, name, phone
          FROM users WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> (integer)
CREATE FUNCTION user_from_seq(seq_ text)
  RETURNS TABLE(user_ integer) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id FROM users WHERE seq = seq_::integer;
    IF not found THEN raise 'no such seq' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text, text)[0..10]
CREATE FUNCTION list_users(after_ text, search_ text)
  RETURNS TABLE(user_ integer, username_ text, name_ text, phone_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, username, name, phone FROM users
          WHERE role = 'user' AND (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', username, name, phone) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_password(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM revoke_user(user_);
    UPDATE users SET credential = new_ WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_name(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET name = new_ WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_phone(user_ text, new_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE users SET phone = new_ WHERE id = user_::integer;
    IF not found THEN raise 'no such user' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>

-- <editor-fold desc="newss">
--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text)[0..10]
CREATE FUNCTION list_newss(after_ text, search_ text)
  RETURNS TABLE(news_ integer, body_ text, date_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, body, to_char(date, 'YYYY-MM-DD HH24:MI:SS') FROM newss
          WHERE (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', body, to_char(date, 'YYYY-MM-DD HH24:MI:SS')) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text) -> ()
CREATE FUNCTION add_news(body_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO newss(body, date)
        VALUES (body_, now());
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION modify_news(news_ text, body_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE newss SET body = body_ WHERE id = news_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION remove_news(news_ text)
  RETURNS void AS
$BODY$
BEGIN
    DELETE FROM newss WHERE id = news_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

-- </editor-fold>

-- <editor-fold desc="autos">
--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text, integer, text)[0..10]
CREATE FUNCTION list_autos(after_ text, search_ text)
  RETURNS TABLE(auto_ integer, name_ text, description_ text, price_ integer, available_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT
            id, name, description, price,
            CASE WHEN available
                THEN 'true'
                ELSE 'false'
            END
          FROM online_autos
          WHERE (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', name, description, price) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text, integer)[0..10]
CREATE FUNCTION list_available_autos(after_ text, search_ text)
  RETURNS TABLE(auto_ integer, name_ text, description_ text, price_ integer) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, name, description, price FROM online_autos
          WHERE available
                AND (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', name, description, price) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (text, text, text) -> ()
CREATE FUNCTION add_auto(name_ text, description_ text, price_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO autos(name, description, price, removed)
        VALUES (name_, description_, price_::integer, false);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION remove_auto(auto_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE autos SET removed = true WHERE id = auto_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>

-- <editor-fold desc="orders">
--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text, text, text, integer, order_state)[0..10]
CREATE FUNCTION list_orders(after_ text, search_ text)
  RETURNS TABLE(order_ integer, orderer_ text, phone_ text, auto_name_ text, auto_description_ text, price_ integer, state_ order_state) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, orderer, phone, auto_name, auto_description, price, state
          FROM verbose_orders
          WHERE (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', orderer, phone, auto_name, auto_description, price) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, text) -> (integer, text, text, integer, order_state)[0..10]
CREATE FUNCTION list_my_orders(user_ text, after_ text, search_ text)
  RETURNS TABLE(order_ integer, auto_name_ text, auto_description_ text, price_ integer, state_ order_state) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, auto_name, auto_description, price, state
          FROM verbose_orders
          WHERE orderer_id = user_::integer
                AND (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', auto_name, auto_description, price) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION add_order(orderer_ text, auto_ text)
  RETURNS void AS
$BODY$
BEGIN
    PERFORM * FROM online_autos WHERE id = auto_::integer AND available;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;

    INSERT INTO orders(orderer, auto, state)
        VALUES (orderer_::integer, auto_::integer, 'fresh');
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION cancel_my_order(orderer_ text, order_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE orders SET state = 'canceled'
        WHERE id = order_::integer
          AND orderer = orderer_::integer
          AND state = 'fresh';
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION cancel_order(order_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE orders SET state = 'canceled'
        WHERE id = order_::integer
          AND state = 'fresh';
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION deliver_order(order_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE orders SET state = 'delivered'
        WHERE id = order_::integer
          AND state = 'fresh';
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer) -> ()
CREATE FUNCTION return_order(order_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE orders SET state = 'returned'
        WHERE id = order_::integer
          AND state = 'delivered';
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;
-- </editor-fold>

-- <editor-fold desc="feedbacks">
--------------------------------------------------------------------------------
-- Function: (integer, text) -> (integer, text, text, text)[0..10]
CREATE FUNCTION list_feedbacks(after_ text, search_ text)
  RETURNS TABLE(feedback_ integer, who_ text, body_ text, reply_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT
            feedbacks.id,
            format('%s（%s）', users.name, users.username),
            feedbacks.body,
            feedbacks.reply
          FROM feedbacks JOIN users ON feedbacks.who = users.id
          WHERE (after_::integer < 0 OR feedbacks.id < after_::integer)
                AND concat_ws(' ', format('%s（%s）', users.name, users.username), body, reply) ILIKE concat('%', search_, '%')
          ORDER BY feedbacks.id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer, text) -> (integer, text, text)[0..10]
CREATE FUNCTION list_my_feedbacks(user_ text, after_ text, search_ text)
  RETURNS TABLE(feedback_ integer, body_ text, reply_ text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT id, body, reply FROM feedbacks
          WHERE who = user_::integer
                AND (after_::integer < 0 OR id < after_::integer)
                AND concat_ws(' ', body, reply) ILIKE concat('%', search_, '%')
          ORDER BY id DESC LIMIT 10;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION add_feedback(who_ text, body_ text)
  RETURNS void AS
$BODY$
BEGIN
    INSERT INTO feedbacks(who, body)
        VALUES (who_::integer, body_);
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, integer) -> ()
CREATE FUNCTION remove_my_feedback(who_ text, feedback_ text)
  RETURNS void AS
$BODY$
BEGIN
    DELETE FROM feedbacks WHERE who = who_::integer AND id = feedback_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;

--------------------------------------------------------------------------------
-- Function: (integer, text) -> ()
CREATE FUNCTION reply_feedback(feedback_ text, reply_ text)
  RETURNS void AS
$BODY$
BEGIN
    UPDATE feedbacks SET reply = reply_ WHERE id = feedback_::integer;
    IF not found THEN raise 'outdated' USING errcode = 'NOTFD'; END IF;
END;
$BODY$
  LANGUAGE plpgsql
  SECURITY DEFINER;


-- </editor-fold>

-- </editor-fold>

-- <editor-fold desc="roles">
--------------------------------------------------------------------------------
-- Role: ars
CREATE ROLE ars LOGIN
  ENCRYPTED PASSWORD 'md5ad75eb4d7b8eff4b9814ff67a15df3be'  -- md5 for 'ars'
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION
  CONNECTION LIMIT 10
  VALID UNTIL '2016-02-22 23:59:59';
-- </editor-fold>

--------------------------------------------------------------------------------
-- Bootstrap
SELECT * FROM bootstrap();

--------------------------------------------------------------------------------
-- Transaction end
COMMIT;

